package com.ewili.x.special.dao.inter;

import com.ewili.x.special.entity.HisRecord;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;

import java.util.Date;
import java.util.List;

public interface HisRecordDao extends JpaRepository<HisRecord, Long> {

    HisRecord findTopByStockCodeOrderByDateDesc(Long stockCode);

    @Query(value = "SELECT id FROM HisRecord h WHERE  stockCode = ?1 AND date = ?2")
    Object isExists(String stockCode, Date date);

    @Query(value = "SELECT NEW com.ewili.x.special.entity.HisRecord(o.stockCode, MAX(o.date)) FROM HisRecord o GROUP BY o.stockCode")
    List<HisRecord> findStockCodeLastDates();

    @Query(value = "select date_format(date, '%Y') as 'year'  from his_record WHERE stock_code = ?1 AND date >=(SELECT last_finance_report_date FROM stock WHERE code =  ?1) group by date_format(date, '%Y')",
            nativeQuery = true)
    List<String> findYearByCode(String code);

    @Query(value = "SELECT NEW com.ewili.x.special.entity.HisRecord(o.stockCode, MIN(o.date)) FROM HisRecord o GROUP BY o.stockCode ORDER BY o.date DESC")
    List<HisRecord> findStockCodeMinTradeDate();

    @Query(value = "FROM HisRecord o WHERE o.stockCode = ?1 AND o.date > ?2 AND o.date <= ?3  ORDER BY o.date ASC")
    List<HisRecord> findHisRecordByStockCodeAndDateAferAndDateBefore(String stockCode, Date startDate, Date endDate);

    @Query(value = "SELECT stockCode FROM HisRecord o GROUP BY stockCode HAVING COUNT(*) < ?1")
    List<String> findDaysOnMarketStocks(long days);

    List<HisRecord> findHisRecordByStockCodeOrderByDateDesc(String stockCode);

    List<HisRecord> findHisRecordByStockCodeOrderByDateAsc(String stockCode);

    @Query(value = "SELECT stock_code FROM his_record GROUP BY stock_code HAVING datediff(MAX(date), MIN(date))<360", nativeQuery = true)
    List<String> findDaysMarketStocks(long days);

    @Query(value = "SELECT\n" +
            "\ta.stock_code,\n" +
            "\ta.date,\n" +
            "\ta.total_value,\n" +
            "\tb.sum,\n" +
            "\tc.pb\n" +
            "FROM\n" +
            "\t(\n" +
            "\t\tSELECT\n" +
            "\t\t\tstock_code,\n" +
            "\t\t\tdate,\n" +
            "\t\t\ttotal_value\n" +
            "\t\tFROM\n" +
            "\t\t\this_record\n" +
            "\t\tWHERE\n" +
            "\t\t\tstock_code = ?1\n" +
            "\t\tORDER BY\n" +
            "\t\t\tdate DESC\n" +
            "\t) AS a\n" +
            "\n" +
            "LEFT JOIN `value` c\n" +
            "\n" +
            "ON a.stock_code = c.`code` AND a.date = c.date\n" +
            "\n" +
            "LEFT JOIN (\n" +
            "\tSELECT\n" +
            "\t\tSUM(change_amount) AS sum,\n" +
            "\t\t`code`,\n" +
            "\t\tdate\n" +
            "\tFROM\n" +
            "\t\texecs\n" +
            "\tWHERE\n" +
            "\t\t`code` = ?1\n" +
            "\tGROUP BY\n" +
            "\t\tdate\n" +
            "\tORDER BY\n" +
            "\t\tdate DESC\n" +
            ") AS b ON a.stock_code = b. CODE\n" +
            "AND a.date = b.date order by date ASC;", nativeQuery = true)
    List<Object[]> findHisRecordExecs(String stockCode);

    @Query(value = "SELECT\n" +
            "\tc.date,\n" +
            "\te.sub_dis,\n" +
            "\tb.sum,\n" +
            "\tc.val,\n" +
            "\td.hy_val\n" +
            "FROM\n" +
            " (\n" +
            "\tSELECT\n" +
            "\t\tcount(*) AS val,\n" +
            "\t\tdate\n" +
            "\tFROM\n" +
            "\t\t`value`\n" +
            "\tWHERE\n" +
            "\t\tpcf > 0\n" +
            "\tAND pcf <= 0.5\n" +
            "\tGROUP BY\n" +
            "\t\tdate\n" +
            ") AS c\n" +
            "LEFT JOIN (\n" +
            "\tSELECT\n" +
            "\t\tcount(DISTINCT(hy_code)) AS hy_val,\n" +
            "\t\tdate\n" +
            "\tFROM\n" +
            "\t\t`value`\n" +
            "\tWHERE\n" +
            "\t\tpcf > 0\n" +
            "\tAND pcf <= 0.5\n" +
            "\tGROUP BY\n" +
            "\t\tdate\n" +
            ") AS d ON c.date = d.date\n" +
            "LEFT JOIN (\n" +
            "\tSELECT\n" +
            "\t\tnet_cash AS sum,\n" +
            "\t\tdate\n" +
            "\tFROM\n" +
            "\t\texecs_market\n" +
            "WHERE\n" +
            "\tnet_cash>0\n" +
            ") AS b ON c.date = b.date\n" +
            "LEFT JOIN (\n" +
            "\tSELECT\n" +
            "\t\tpre_sub_dis AS sub_dis,\n" +
            "\t\tdate\n" +
            "\tFROM\n" +
            "\t\tblock_trading_market\n" +
            "WHERE\n" +
            "\tpre_sub_dis>0\n" +
            ") AS e ON c.date = e.date\n" +
            "ORDER BY\n" +
            "\tdate ASC;", nativeQuery = true)
    List<Object[]> findWholeMarketsExecs();

    @Query(value = "SELECT COUNT(1) FROM HisRecord where stockCode=?1 AND date=?2")
    Integer existsByStockCodeAndDate(String stockCode, Date hisRecordDate);


    @Query(value = "SELECT max(date) from HisRecord where stockCode= ?1")
    Date getMaxDate(String code);

    List<HisRecord> findHisRecordByStockCode(String code);

    List<HisRecord> findHisRecordByStockCodeAndAdj(String code, double adj);
}
